//
//  TestModel.swift
//  hear
//
//  Created by tiger on 2017/11/23.
//  Copyright © 2017年 Hunan Qinxin Information Technology co.,Ltd. All rights reserved.
//

import UIKit
import SQLite


//MARK: - 数据库管理类
internal class NBADBManager {
    internal static let shared: NBADBManager = NBADBManager()
    /// 数据库
    var db: Connection!
    let nbaClubTable = Table("nba_club")
    let nbaPlayerTable = Table("nba_player")
    let nbaWorkerTable = Table("nba_worker")
    let nbaBossTable = Table("nba_boss")
    let nbaCoachTable = Table("nba_coach")
    
    internal static func document(subPath: String) -> URL {
        let directoryURL = FileManager.default.urls(for: .documentDirectory,
                                                    in: .userDomainMask)[0]
        return directoryURL.appendingPathComponent(subPath)
    }
    
    init() {
        do {
            let fileUrl = NBADBManager.document(subPath: "sqlite3")
            let database = try Connection(fileUrl.path)
            self.db = database
        } catch {
            LogUtil.e("初始化错误")
        }
    }
    
    /// 创建表
    internal func createTables(success: ()->Swift.Void,failed: (Error)->Void) {
        do {
            try self.createClubTable()
            try self.createPlayerTable()
            try self.createWorkerTable()
            try self.createClubBossTable()
            try self.createClubCoachTable()
        } catch {
            failed(error)
        }
        success()
    }
    
    /// 迁移老版本数据
    func moveOldVersionData() {
        
    }
}


//MARK: - 故事下载数据库操作
internal extension NBADBManager {
//    public func initClubs() {
//        let clubIds = ["100","101","102","103","104","105","106","107"]
//        let clubCNNames = ["雷霆","火箭","骑士","马刺","勇士","凯尔特人","湖人","76人"]
//        let clubPlaces = ["奥克兰","休斯敦","克利夫兰","圣安东尼奥","金州","波士顿","洛杉矶","费城"]
//        let clubENNames = ["Thunder","Rockets","Cavaliers","Spurs","Warriors","Celtics","Lakers","76ers"]
//        let clubIcons = ["logo_oklahoma_city_thunder","logo_houston_rockets","log_cleveland_cavaliers","logo_san_antonio_spurs","logo_golden_state_warriors","logo_boston_celtics","logo_los_angeles_lakers","logo_philadelphia_76ers"]
//        for index in 0..<clubIds.count {
//
//        }
//    }

   
    
    
//    /// 添加俱乐部
//    ///
//    /// - Parameter story: 故事
//    func insertClub(club: NBAClub) throws {
//        if String.hasCharacter(club.clubId) == false {
//            return
//        }
//        let mediaId = story.versionId ?? ""  //版本ID是唯一的 应该作为主键
//        let mediaAssistId = story.storyId ?? ""
//        let mediaType = StoryItemType.Voice.rawValue
//        let mediaLen = story.getStoryLen()
//        let mediaCover = story.Cover ?? ""
//        let name = story.getTitle()
//        let localPath = ""
//        let url = story.url ?? ""
//        let status = MediaDownloadState.suspended.rawValue
//        let bytesWritten = 0
//        let bytesExpectedWritten = 0
//        let personName = story.getArtist()
//        let jsonText  = story.jsonString
//        let createTime = Date().timeIntervalSince1970
//
//        if String.hasCharacter(mediaId) == false || String.hasCharacter(url) == false || String.hasCharacter(jsonText) == false {
//            throw(NSError(domain: "添加错误", code: 401, userInfo: [NSLocalizedDescriptionKey: "故事信息错误"]))
//        }
//
//        let downloadColums = MediaDownloadColumn()
//        let insertStory = self.voiceDownloadTable.insert(downloadColums.mediaId <- mediaId,
//                                                         downloadColums.mediaAssistId <- mediaAssistId,
//                                                         downloadColums.mediaType <- mediaType,
//                                                         downloadColums.mediaLen <- mediaLen,
//                                                         downloadColums.mediaCover <- mediaCover,
//                                                         downloadColums.name <- name,
//                                                         downloadColums.localPath <- localPath,
//                                                         downloadColums.url <- url,
//                                                         downloadColums.status <- status,
//                                                         downloadColums.bytesWritten <- bytesWritten,
//                                                         downloadColums.bytesExpectedWritten <- bytesExpectedWritten,
//                                                         downloadColums.personName <- personName,
//                                                         downloadColums.jsonText <- jsonText,
//                                                         downloadColums.createTime <- createTime)
//
//        do {
//            try self.db.run(insertStory)
//            LogUtil.i("Insert Download Story Success")
//        } catch {
//            LogUtil.e(error)
//            throw(error as NSError)
//        }
//    }
//
//    /// 判断故事是否下载完成了
//    ///
//    /// - Parameter story: 故事
//    /// - Returns: 是否下载完成
//    func isStoryDownloaded(url: String) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.voiceDownloadTable.filter(downloadColums.status == MediaDownloadState.completed.rawValue && downloadColums.url == url)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var storys = [Story]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let story = Story(json: json)
//                storys.append(story)
//            }
//            return storys.count > 0
//        } catch {
//            LogUtil.i(error)
//            return false
//        }
//    }
//
//    /// 判断故事是否已经在数据库
//    ///
//    /// - Parameter story: 故事
//    /// - Returns: 是否下载完成
//    func filter(story: Story) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.voiceDownloadTable.filter(downloadColums.url == story.url!)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var storys = [Story]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let story = Story(json: json)
//                storys.append(story)
//            }
//            return storys.count > 0
//        } catch {
//            LogUtil.i(error)
//            return false
//        }
//    }
//
//
//    /// 已经下载完的故事
//    ///
//    /// - Returns: 下载完的故事数组
//    func listDownloadFinishedStory() -> [Story] {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.voiceDownloadTable.filter(downloadColums.status == MediaDownloadState.completed.rawValue).order(downloadColums.createTime.desc)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var storys = [Story]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let story = Story(json: json)
//                story.isLocal = true
//                story.storyId = result[downloadColums.mediaAssistId]
//                story.versionId = result[downloadColums.mediaId]
//                story.storyLen = result[downloadColums.mediaLen]
//                story.Cover = result[downloadColums.mediaCover]
//                story.title = result[downloadColums.name]
//                story.url = result[downloadColums.url]
//                story.ahchor.userNick = result[downloadColums.personName]
//                story.state = .completed
//                storys.append(story)
//            }
//            return storys
//        } catch {
//            LogUtil.i(error)
//            return [Story]()
//        }
//    }
//
//
//    /// 未下载完的故事
//    ///
//    /// - Returns: 故事数组
//    func listDownloadUnfinishedStory() -> [Story] {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.voiceDownloadTable.filter(downloadColums.status != MediaDownloadState.completed.rawValue).order(downloadColums.createTime.desc)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var storys = [Story]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let story = Story(json: json)
//                story.storyId = result[downloadColums.mediaAssistId]
//                story.versionId = result[downloadColums.mediaId]
//                story.storyLen = result[downloadColums.mediaLen]
//                story.Cover = result[downloadColums.mediaCover]
//                story.state = MediaDownloadState(rawValue: result[downloadColums.status])!
//                story.title = result[downloadColums.name]
//                story.url = result[downloadColums.url]
//                story.ahchor.userNick = result[downloadColums.personName]
//                storys.append(story)
//            }
//            return storys
//        } catch {
//            LogUtil.i(error)
//            return [Story]()
//        }
//    }
//
//    func updateStoryProgress(story: Story,bytesWritten: Int,bytesExpectedWritten: Int) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let updateStory = self.voiceDownloadTable.filter(downloadColums.url == story.url!).update(downloadColums.bytesWritten <- bytesWritten,downloadColums.bytesExpectedWritten <- bytesExpectedWritten)
//        do {
//            if try self.db.run(updateStory) > 0 {
//                LogUtil.i("deleted story")
//                return true
//            } else {
//                LogUtil.i("story not found")
//                return false
//            }
//        } catch {
//            LogUtil.i("story delete failed: \(error)")
//            return false
//        }
//    }
//
//    @discardableResult
//    func updateStoryStatus(storyUrl: String,status: MediaDownloadState) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let updateStory = self.voiceDownloadTable.filter(downloadColums.url == storyUrl).update(downloadColums.status <- status.rawValue)
//        do {
//            if try self.db.run(updateStory) > 0 {
//                LogUtil.i("deleted story")
//                return true
//            } else {
//                LogUtil.i("story not found")
//                return false
//            }
//        } catch {
//            LogUtil.i("story delete failed: \(error)")
//            return false
//        }
//    }
//
//    /// 删除下载表中的故事
//    ///
//    /// - Parameter story: 需要删除的故事
//    /// - Returns: 是否删除成功
//    @discardableResult
//    func deleteDownloadStory(story: Story) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let deleteStory = self.voiceDownloadTable.filter(downloadColums.url == story.url!)
//        do {
//            if try db.run(deleteStory.delete()) > 0 {
//                LogUtil.i("deleted story")
//                return true
//            } else {
//                LogUtil.i("story not found")
//                return true
//            }
//        } catch {
//            LogUtil.i("story delete failed: \(error)")
//            return false
//        }
//    }
//}
//
////MARK: - 视频下载数据库操作
//extension DBManager {
//
//
//    /// 添加需要下载的视频
//    ///
//    /// - Parameter video: 需要下载的视频
//    func insertDownloadVideo(video: Video) throws {
//        if String.hasCharacter(video.videoId) == false {
//            return
//        }
//        let mediaId = video.videoId!
//        let mediaAssistId = ""
//        let mediaType = StoryItemType.Video.rawValue
//        let mediaLen = video.getVideoLen()
//        let mediaCover = video.coverUrl ?? ""
//        let name = video.getTitle()
//        let localPath = ""
//        let url = video.playUrl ?? ""
//        let status = MediaDownloadState.suspended.rawValue
//        let bytesWritten = 0
//        let bytesExpectedWritten = 0
//        let personName = video.getArtist()
//        let jsonText  = video.jsonString
//        let createTime = Date().timeIntervalSince1970
//
//        if String.hasCharacter(mediaId) == false || String.hasCharacter(url) == false || String.hasCharacter(jsonText) == false {
//            throw(NSError(domain: "添加错误", code: 401, userInfo: [NSLocalizedDescriptionKey: "故事信息错误"]))
//        }
//
//        let downloadColums = MediaDownloadColumn()
//        let insertVideo = self.videoDownloadTable.insert(downloadColums.mediaId <- mediaId,
//                                                         downloadColums.mediaAssistId <- mediaAssistId,
//                                                         downloadColums.mediaType <- mediaType,
//                                                         downloadColums.mediaLen <- mediaLen,
//                                                         downloadColums.mediaCover <- mediaCover,
//                                                         downloadColums.name <- name,
//                                                         downloadColums.localPath <- localPath,
//                                                         downloadColums.url <- url,
//                                                         downloadColums.status <- status,
//                                                         downloadColums.bytesWritten <- bytesWritten,
//                                                         downloadColums.bytesExpectedWritten <- bytesExpectedWritten,
//                                                         downloadColums.personName <- personName,
//                                                         downloadColums.jsonText <- jsonText,
//                                                         downloadColums.createTime <- createTime)
//
//        do {
//            try self.db.run(insertVideo)
//            LogUtil.i("Insert Download Video Success")
//        } catch {
//            LogUtil.i(error)
//            throw(error as NSError)
//        }
//    }
//
//    /// 判断故事是否下载完成了
//    ///
//    /// - Parameter url: 播放连接
//    /// - Returns: 是否下载完成
//    func isVideoDownloaded(url: String) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.videoDownloadTable.filter(downloadColums.status == MediaDownloadState.completed.rawValue && downloadColums.url == url)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var videos = [Video]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let video = Video(json: json)
//                videos.append(video)
//            }
//            return videos.count > 0
//        } catch {
//            LogUtil.i(error)
//            return false
//        }
//    }
//
//    /// 判断故事是否已经在数据库
//    ///
//    /// - Parameter story: 故事
//    /// - Returns: 是否下载完成
//    func filter(video: Video) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let filterVideo = self.videoDownloadTable.filter(downloadColums.url == video.playUrl!)
//        do {
//            let results = try self.db.prepare(filterVideo)
//            var videos = [Video]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let video = Video(json: json)
//                videos.append(video)
//            }
//            return videos.count > 0
//        } catch {
//            LogUtil.i(error)
//            return false
//        }
//    }
//
//    /// 已经下载完的视频
//    ///
//    /// - Returns: 下载完的视频
//    func listDownloadFinishedVideo() -> [Video] {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.videoDownloadTable.filter(downloadColums.status == MediaDownloadState.completed.rawValue).order(downloadColums.createTime.desc)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var videos = [Video]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let video = Video(json: json)
//                video.videoId = result[downloadColums.mediaId]
//                video.title = result[downloadColums.name]
//                video.videoLen = result[downloadColums.mediaLen]
//                video.coverUrl = result[downloadColums.mediaCover]
//                video.playUrl = result[downloadColums.url]
//                video.ahchor.userNick = result[downloadColums.personName]
//                video.state = .completed
//                videos.append(video)
//            }
//            return videos
//        } catch {
//            LogUtil.i(error)
//            return [Video]()
//        }
//    }
//
//
//    /// 未下载完的故事
//    ///
//    /// - Returns: 视频数组
//    func listDownloadUnfinishedVideo() -> [Video] {
//        let downloadColums = MediaDownloadColumn()
//        let filterStory = self.videoDownloadTable.filter(downloadColums.status != MediaDownloadState.completed.rawValue).order(downloadColums.createTime.desc)
//        do {
//            let results = try self.db.prepare(filterStory)
//            var videos = [Video]()
//            for result in results {
//                let jsonString = result[downloadColums.jsonText]
//                let json = JSON.init(parseJSON: jsonString)
//                let video = Video(json: json)
//                video.videoId = result[downloadColums.mediaId]
//                video.state = MediaDownloadState(rawValue: result[downloadColums.status])!
//                video.videoLen = result[downloadColums.mediaLen]
//                video.coverUrl = result[downloadColums.mediaCover]
//                video.title = result[downloadColums.name]
//                video.playUrl = result[downloadColums.url]
//                video.ahchor.userNick = result[downloadColums.personName]
//                videos.append(video)
//            }
//            return videos
//        } catch {
//            LogUtil.i(error)
//            return [Video]()
//        }
//    }
//
//    func updateVideoProgress(video: Video,bytesWritten: Int,bytesExpectedWritten: Int) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let updateVideo = self.videoDownloadTable.filter(downloadColums.url == video.playUrl!).update(downloadColums.bytesWritten <- bytesWritten,downloadColums.bytesExpectedWritten <- bytesExpectedWritten)
//        do {
//            if try self.db.run(updateVideo) > 0 {
//                LogUtil.i("deleted video")
//                return true
//            } else {
//                LogUtil.i("video not found")
//                return false
//            }
//        } catch {
//            LogUtil.i("video delete failed: \(error)")
//            return false
//        }
//    }
//
//    @discardableResult
//    func updateVideoStatus(videoUrl: String,status: MediaDownloadState) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let updateVideo = self.videoDownloadTable.filter(downloadColums.url == videoUrl).update(downloadColums.status <- status.rawValue)
//        do {
//            if try self.db.run(updateVideo) > 0 {
//                LogUtil.i("deleted video")
//                return true
//            } else {
//                LogUtil.i("video not found")
//                return false
//            }
//        } catch {
//            LogUtil.i("video delete failed: \(error)")
//            return false
//        }
//    }
//
//    /// 删除下载表中的视频
//    ///
//    /// - Parameter story: 需要删除的视频
//    /// - Returns: 是否删除成功
//    @discardableResult
//    func deleteDownloadVideo(video: Video) -> Bool {
//        let downloadColums = MediaDownloadColumn()
//        let deleteVideo = self.videoDownloadTable.filter(downloadColums.url == video.playUrl!)
//        do {
//            if try db.run(deleteVideo.delete()) > 0 {
//                LogUtil.i("deleted video")
//                return true
//            } else {
//                LogUtil.i("video not found")
//                return true
//            }
//        } catch {
//            LogUtil.i("video delete failed: \(error)")
//            return false
//        }
//    }
//}
//
//
//
//
//public class NBADataManager: NSObject {
//
//    public static let NBAClubTable = "nba_club"
//    public static let NBAPlayersTable = "nba_players"
//    public static let NBACoachTable = "nba_coach"
//    public static let NBAScheduleTable = "nba_schedule"
//    public static let NBAClubBoss = "nba_club_boss"
//
//    public static let shared = NBADataManager()
//    public lazy var dbQueue: FMDatabaseQueue = FMDatabaseQueue(path: NBADataManager.documentDirectory(subPath: "nba_data.sqlite"))
//
//    public var successBlock: (() -> (Void))?
//    public var failedBlock: (() -> (Void))?
//
//    func initDb(sucess: @escaping ()->Void,failed: @escaping  ()->Void) {
//        successBlock = sucess
//        failedBlock = failed
//        self.createTables()
//        self.updateTables()
//    }
//
//    /// create db tables
//    private func createTables() {
//        let clubTableSelect = "SELECT name FROM sqlite_master WHERE type='table' AND name='\(NBADataManager.NBAClubTable)';"
//        var clubTableExist = false
//        self.dbQueue.inDatabase { (db) in
//            do {
//                let ret = try db.executeQuery(clubTableSelect, values: nil)
//                while(ret.next() == true) {
//                    clubTableExist = true
//                }
//            } catch {
//
//            }
//        }
//        if clubTableExist == false {
//            self.initClubs()
//            successBlock?()
//            successBlock = nil
//        } else {
//            successBlock?()
//            successBlock = nil
//        }
//
//    }
//
//    //初始化俱乐部
//    private func initClubs() {
//        let sqlCreateNewsSources = "CREATE TABLE IF NOT EXISTS \(NBADataManager.NBAClubTable) \n" +
//            "(id TEXT PRIMARY KEY, \n" +
//            "name_cn TEXT NOT NULL, \n" +
//            "name_en TEXT NOT NULL, \n" +
//            "place TEXT NOT NULL, \n" +
//        "icon TEXT);"
//        self.dbQueue.inDatabase { (db) in
//            db.executeUpdate(sqlCreateNewsSources, withArgumentsIn: [])
//        }
//
//        let clubIds = ["100","101","102","103","104","105","106","107"]
//        let clubCNNames = ["雷霆","火箭","骑士","马刺","勇士","凯尔特人","湖人","76人"]
//        let clubPlaces = ["奥克兰","休斯敦","克利夫兰","圣安东尼奥","金州","波士顿","洛杉矶","费城"]
//        let clubENNames = ["Thunder","Rockets","Cavaliers","Spurs","Warriors","Celtics","Lakers","76ers"]
//        let clubIcons = ["logo_oklahoma_city_thunder","logo_houston_rockets","log_cleveland_cavaliers","logo_san_antonio_spurs","logo_golden_state_warriors","logo_boston_celtics","logo_los_angeles_lakers","logo_philadelphia_76ers"]
//
//        let sql = "INSERT INTO \(NBADataManager.NBAClubTable) \n" +
//            "(id ,name_cn,name_en,place,icon)\n" +
//            "VALUES\n" +
//        "(?, ?, ?, ?);"
//        for index in 0..<clubIds.count {
//            var values = [Any]()
//            values.append(clubIds[index])
//            values.append(clubCNNames[index])
//            values.append(clubENNames[index])
//            values.append(clubPlaces[index])
//            values.append(clubIcons[index])
//            self.dbQueue.inTransaction { (db, finish) in
//                do {
//                    try db.executeUpdate(sql, values: values)
//                } catch {
//
//                }
//            }
//        }
//    }
//
//    private func initSchedule() {
//        let sqlCreateScheduleTables = "CREATE TABLE IF NOT EXISTS \(NBADataManager.NBAScheduleTable) \n" +
//            "(id TEXT PRIMARY KEY, \n" +
//            "home_club_id TEXT NOT NULL, \n" +
//            "visiting_club_id TEXT NOT NULL, \n" +
//            "time NOT NULL Integer);"
//        self.dbQueue.inDatabase { (db) in
//            db.executeUpdate(sqlCreateScheduleTables, withArgumentsIn: [])
//        }
//        let clubIds = ["100","101","102","103","104","105","106","107"]
//        var allGameSchedules = [NBAClubGameSchedule]()
//        let currentDate = Int(Date().timeIntervalSince1970)
//        var scheduleId = 10
//        for i in 0..<8 {  //赛季为14天
//            for j in 0..<8 {
//                if i == j {
//                    continue
//                }
//                let gameSchedule = NBAClubGameSchedule()
//                gameSchedule.homeClubId = clubIds[i]
//                gameSchedule.visitingClubId = clubIds[j]
//                scheduleId += 1
//                gameSchedule.scheduleId = String(format: "%d",scheduleId+currentDate)
//                gameSchedule.time = Date().timeIntervalSince1970 + Double((i+j)*24*60)
//                allGameSchedules.append(gameSchedule)
//            }
//        }
//
//        let sql = "INSERT INTO \(NBADataManager.NBAScheduleTable) \n" +
//            "(id ,home_club_id,visiting_club_id,time)\n" +
//            "VALUES\n" +
//        "(?,?,?,？);"
//        for index in 0..<allGameSchedules.count {
//            var values = [Any]()
//            let gameSchedule = allGameSchedules[index]
//            values.append(gameSchedule.scheduleId)
//            values.append(gameSchedule.homeClubId)
//            values.append(gameSchedule.visitingClubId)
//            values.append(gameSchedule.time)
//            self.dbQueue.inTransaction { (db, finish) in
//                do {
//                    try db.executeUpdate(sql, values: values)
//                } catch {
//
//                }
//            }
//        }
//    }
//
//    /// if tables propretys need update
//    private func updateTables() {
//
//    }
//
//    public static func documentDirectory(subPath: String) -> String {
//        let path = (NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last!) as NSString
//        return path.appendingPathComponent((subPath as NSString).lastPathComponent)
//    }
}
